/**
 * 
 */
package org.compiere.report;

import java.io.File;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.CellFormat;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormats;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.adempiere.exceptions.AdempiereException;
import org.compiere.apps.ADialog;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.X_hrm_order;
import org.compiere.process.ProcessInfo;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import org.eevolution.model.X_HR_Department;

import extend.org.compiere.hrm.ExcelCell;
import extend.org.compiere.utils.Util;

/**
 * @author V.Sokolov
 *
 */
public class ReportRoutineBusiness extends SvrProcess {
	
	/** Current context		*/
	private Properties m_ctx;
	/**	 */
	private ProcessInfo pi;
	/** */
	private int HR_Department_ID = 0;
	/** */
	private int HR_Block_ID = 0;
	/** */
	private String path = "";
	/** */
	X_HR_Department department = null;
	/** */
	X_HR_Department block = null;
	/** */
	Timestamp startDate;
	/** */
	Timestamp endDate;

	/* 
	 */
	@Override
	protected void prepare() {
		
		log.info("Prepare statement for report List of Routine Business");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++)	{
			String name = para[i].getParameterName();
			if (name == null); 
				//
			else if (name.compareTo("HR_Block_ID") == 0 && para[i].getParameter() != null)
				HR_Block_ID = new BigDecimal(Integer.parseInt(para[i].getParameter().toString())).intValue();
			else if (name.compareTo("HR_Department_ID") == 0 && para[i].getParameter() != null)
				HR_Department_ID = new BigDecimal(Integer.parseInt(para[i].getParameter().toString())).intValue();
			else if (name.toLowerCase().endsWith("directory"))
				path = (String)para[i].getParameter();
			else if (name.compareTo("StartDate") == 0){
				startDate = (Timestamp) para[i].getParameter();
				endDate = (Timestamp) para[i].getParameter_To();
			}
			else
			{
				log.log(Level.INFO, "Unknown Parameter: " + name);
			}
		}
		
		if(path.isEmpty())
			path = Util.localFilePath;
		else
			path = path + System.getProperty("file.separator");

		department = new X_HR_Department(getCtx(), HR_Department_ID, null);
		block = new X_HR_Department(getCtx(), HR_Block_ID, null);

	}

	/* 
	 */
	@Override
	protected String doIt() throws Exception {
		//Types of cells for
		Label label;
		Number number;
		Formula formula;
		// There are checks for a specified pattern
		String templateName = "SampleRoutineBusiness";
		
		//
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, templateName);
		if(entry == null)
			throw new AdempiereException(Msg.translate(m_ctx, "NotFoundTemplate")+" "+templateName);
				
		// We define a path to generate
		String fileExtension = entry.getName().substring(entry.getName().lastIndexOf("."),entry.getName().length());
		StringBuffer fullPath = new StringBuffer(path); 
		fullPath.append("List of Routine Business").append(fileExtension);
				
		// 
		File templateCopy = new File(fullPath.toString());
		File temporaryFile = entry.getFile(Util.localFilePath+templateName);
		Workbook tableBook = null;
		WritableWorkbook copy = null;
		WritableSheet sheet = null;
		WritableSheet sheet1 = null;
		 try {
			 tableBook = Workbook.getWorkbook(temporaryFile);
			 copy = Workbook.createWorkbook(templateCopy, tableBook);
			 sheet = copy.getSheet(0);
			 sheet1 = copy.getSheet(1);
		} catch (Exception e) {
		  	ADialog.error(999, null, "Error tableBook. " + e.getMessage());
		   	return "Error tableBook.";
		}

		ExcelCell cellStart =  Util.getCellStart(tableBook,">>");
		
		// Standart style cell
		WritableCellFormat borderStyle = new WritableCellFormat();
		borderStyle.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
		borderStyle.setAlignment(Alignment.CENTRE);
		borderStyle.setVerticalAlignment(VerticalAlignment.CENTRE);
		borderStyle.setWrap(true);
		// Department style cell
		WritableFont fontDepartment = new WritableFont (WritableFont.TIMES, 12, WritableFont.BOLD);
		WritableCellFormat cellDepartment = new WritableCellFormat(fontDepartment);
		cellDepartment.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
		cellDepartment.setAlignment(Alignment.LEFT);
		cellDepartment.setVerticalAlignment(VerticalAlignment.CENTRE);
		cellDepartment.setWrap(true);
		cellDepartment.setBackground(Colour.TAN);
		// Title style cell
		WritableFont fontTitle = new WritableFont (WritableFont.TIMES, 14, WritableFont.NO_BOLD);
		WritableCellFormat titleStyle = new WritableCellFormat(fontTitle);
		titleStyle.setAlignment(Alignment.CENTRE);
		titleStyle.setVerticalAlignment(VerticalAlignment.CENTRE);
		// Percent format
		WritableFont fontPercent = new WritableFont (WritableFont.TIMES, 12, WritableFont.BOLD);
		WritableCellFormat percentf = new WritableCellFormat(fontPercent, NumberFormats.PERCENT_INTEGER);
		percentf.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
		percentf.setAlignment(Alignment.CENTRE);
		percentf.setVerticalAlignment(VerticalAlignment.CENTRE);
		 // Total style cell
	    WritableFont fontTotal = new WritableFont (WritableFont.TIMES, 14, WritableFont.BOLD);
	    WritableCellFormat totalStyle = new WritableCellFormat(fontTotal);
	    totalStyle.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
	    totalStyle.setAlignment(Alignment.CENTRE);
	    totalStyle.setVerticalAlignment(VerticalAlignment.CENTRE);
	    totalStyle.setBackground(Colour.GRAY_25);
		
	    // header report
	    WritableCell readCell = sheet.getWritableCell(1, 1);
		CellFormat readFormat = readCell.getCellFormat();
		label = new Label(1, 1, sheet.getCell(1, 1).getContents()+Util.getOrgInfo().getDescription(), readFormat);
		sheet.addCell(label);
	    
	    // title
		if(HR_Block_ID != 0){
			label = new Label(2, 2, "Блока "+block.getName(), titleStyle);
			sheet.addCell(label);
		}
		
		Object[] param = new Object[]{Util.getMonthName(new Timestamp(startDate.getTime()), Env.getAD_Language(getCtx()), false), 
				  Integer.toString(new org.joda.time.DateTime(startDate.getTime()).getYear()),
				  Util.getMonthName(new Timestamp(endDate.getTime()), Env.getAD_Language(getCtx()), false),
				  Integer.toString(new org.joda.time.DateTime(endDate.getTime()).getYear())};
		if(new org.joda.time.DateTime(startDate.getTime()).getMonthOfYear() != new org.joda.time.DateTime(endDate.getTime()).getMonthOfYear())
		label = new Label(4, 3, sheet.getCell(4, 3).getContents()+MessageFormat.format(" {0} {1} - {2} {3} года", param), titleStyle);
		else
		label = new Label(4, 3, MessageFormat.format(" за {0} месяц {1} года", param), titleStyle);
		sheet.addCell(label);
		
		RecordOfDepartment[] lines = getValues();
		int col = cellStart.getC(); // offset by the number of first columns
	    int row = cellStart.getR();
	    int n = 0;
	    int num = 1;
	    int rowC = 0;
	    int actual = 0;
	    int disCnt = 0;
	    double avg = 0;
	    int sumDisCnt = 0;
	    double sumAvg = 0.;
	    int sumActual = 0;
	    for(int j = row; j < (row+lines.length); j++){
	    	
	    	sheet.setRowView(j, 600);
	    	
	    	disCnt = getDisCnt(lines[n].getDepartment_id());
	    	avg = getAvgEmp(lines[n].getDepartment_id());
	    	actual = getActualNumberDep(lines[n].getDepartment_id());
	    	// #
	    	number = new Number(cellStart.getC(), j, num, borderStyle); 
	    	sheet.addCell(number);
	    	//	Department name
	    	label = new Label(col+1, j, lines[n].getDepartment(), cellDepartment);
	    	sheet.addCell(label);
	    	// Actual number
	    	number = new Number(col+2, j, actual, borderStyle); 
	    	sheet.addCell(number);
	    	// AVG
	    	number = new Number(col+3, j, avg, borderStyle); 
	    	sheet.addCell(number);
	    	// Dismissal
	    	number = new Number(col+4, j, disCnt, borderStyle);
	    	sheet.addCell(number);
	    	// % Percentage of turnover
	    	if(avg !=0 )
	    		number = new Number(col+5, j, disCnt/avg , percentf);
	    	else
	    		number = new Number(col+5, j, 0 , percentf);
			sheet.addCell(number);
			
			sumDisCnt = sumDisCnt + disCnt;
			sumAvg = sumAvg + avg;
			sumActual = sumActual + actual;
	    	
			rowC = j;			
	    	n++;
	    	num++;
	    }
		
    	// Total
	    rowC++;
	    sheet.mergeCells(col, rowC, col+1, rowC);
    	sheet.setRowView(rowC, 700);
    	// Total
    	label = new Label(col, rowC, "ИТОГО", totalStyle); 
	    sheet.addCell(label);
    	// Sum actual
	   	number = new Number(col+2, rowC, sumActual, totalStyle); 
	   	sheet.addCell(number);  
	   	// Sum AVG
	   	number = new Number(col+3, rowC, sumAvg, totalStyle); 
	   	sheet.addCell(number);  
	   	// Sum Dismissal
	   	number = new Number(col+4, rowC, sumDisCnt, totalStyle); 
	   	sheet.addCell(number); 
	   	// Total percent
	   	if(sumAvg != 0)
	   		number = new Number(col+5, rowC, Math.round((sumDisCnt/sumAvg)*100), totalStyle);
	   	else
	   		number = new Number(col+5, rowC, 0, totalStyle);
	   	sheet.addCell(number); 
	   	
	   	// Diagram
	   	formula = new Formula(col+3, 1, sheet.getName()+"!F"+(rowC+1));
		sheet1.addCell(formula);
		formula = new Formula(col+4, 1, sheet.getName()+"!G"+(rowC+1));
		sheet1.addCell(formula);
	    
	    if(HR_Block_ID == 0 && HR_Department_ID == 0)
	    	sheet.removeRow(2);
	    
	    copy.write();
	    copy.close();
	    tableBook.close();
	    temporaryFile.delete();
		
		// Open the file with the application associated
		String fileName = fullPath.toString();
		Util.launchFile(new File(fileName));
			 	
		return Msg.translate(m_ctx, "Success");
	}
	
	private int getDisCnt(int departmentId){
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;		
		
		StringBuffer sql = new StringBuffer("SELECT count(*) FROM HRM_EMPLOYEE  \n");
		sql.append("LEFT JOIN HR_EMPLOYEE ON HR_EMPLOYEE.C_BPARTNER_ID=HRM_EMPLOYEE.C_BPARTNER_ID \n");
		sql.append("		AND HR_EMPLOYEE.STARTDATE = (SELECT MAX(H.STARTDATE) FROM HR_EMPLOYEE H WHERE H.C_BPARTNER_ID=HR_EMPLOYEE.C_BPARTNER_ID) \n");
		sql.append("JOIN HRM_ORDER ON HRM_ORDER.HRM_ORDER_ID=HRM_EMPLOYEE.HRM_ORDER_ID \n");
		sql.append("WHERE HRM_EMPLOYEE.AD_CLIENT_ID=? \n");
		sql.append("AND HRM_ORDER.HRM_ORDERTYPE='"+X_hrm_order.HRM_ORDERTYPE_Dismissal+"' \n");
		sql.append("AND HRM_ORDER.STARTDATE BETWEEN "+DB.TO_DATE(startDate, true)+" AND "+DB.TO_DATE(endDate, true)+" \n");
		sql.append("AND HR_EMPLOYEE.HR_DEPARTMENT_ID=? ");
		
		try
		{
			pstmt = DB.prepareStatement(sql.toString(),null);
			pstmt.setInt(1, getAD_Client_ID());
			pstmt.setInt(2, departmentId);
			rs = pstmt.executeQuery();	
					
			if (rs.next ())
				return rs.getInt(1);
		}
		catch (SQLException e)
		{
			log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}
		
		return 0;
	}
	
	private double getAvgEmp(int departmentId){
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;		
		
		StringBuffer sql = new StringBuffer("with tt as ( \n");
		sql.append(" 	select "+DB.TO_DATE(startDate, true)+" - (interval '1' month * generate_series(0,3)) as dd \n");
		sql.append("), \n t1 as ( \n select a.*,  ( \n");
		sql.append("select count(*) from (select t.c_bpartner_id from hr_employee t \n"
					+"join hr_contract r on r.c_bpartner_id=t.c_bpartner_id and r.hrm_contracttype='1' \n" 
					+"where a.dd between t.startdate and t.enddate \n" 
					+"and t.hr_department_id = ? \n" +
					"GROUP BY T.C_BPARTNER_id) t \n");
		sql.append(") as cc from tt a \n ) \n select round(avg(cc),2) from t1");
		
		try
		{
			pstmt = DB.prepareStatement(sql.toString(),null);
			pstmt.setInt(1, departmentId);
			rs = pstmt.executeQuery();	
					
			if (rs.next ())
				return rs.getDouble(1);
		}
		catch (SQLException e)
		{
			log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}
		
		return 0;
	}
	
	private int getActualNumberDep(int departmentId){
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;		
		
		StringBuffer sql = new StringBuffer("select count(*)::numeric from ( \n");
		sql.append("select t.c_bpartner_id from hr_employee t \n");
		sql.append("join hr_contract r on r.c_bpartner_id=t.c_bpartner_id and r.hrm_contracttype='1' \n");
		sql.append("where ? between t.startdate and t.enddate and t.enddate >= ?  \n");
		sql.append("and t.hr_department_id = ? \n");
		sql.append("and t.ad_client_id=? \n");
		sql.append("GROUP BY T.C_BPARTNER_id) t ");
		
		try
		{
			pstmt = DB.prepareStatement(sql.toString(),null);
			pstmt.setTimestamp(1, startDate);
			pstmt.setTimestamp(2, endDate);
			//pstmt.setTimestamp(3, startDate);
			//pstmt.setTimestamp(4, endDate);
			pstmt.setInt(3, departmentId);
			pstmt.setInt(4, getAD_Client_ID());
			rs = pstmt.executeQuery();	
					
			if (rs.next ())
				return rs.getInt(1);
		}
		catch (SQLException e)
		{
			log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}
		
		return 0;
	}
	
	private RecordOfDepartment[] getValues(){
		
		RecordOfDepartment values = new RecordOfDepartment();
		List<RecordOfDepartment> list = new ArrayList<RecordOfDepartment>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;		
		
		StringBuffer sql = new StringBuffer("SELECT t.hr_department_id AS department_id, t.name AS department \n" +
				"FROM hr_department t \n" +
				"JOIN ad_treenodedep ON ad_treenodedep.node_id=t.hr_department_id \n");
		
		sql.append(" WHERE t.ad_client_id=? AND t.hrm_units='D' AND t.isActive='Y' \n");
		if (HR_Department_ID != 0)
			sql.append(" AND t.hr_department_id=? \n");
		else if(HR_Block_ID !=0)
			sql.append(" AND ad_treenodedep.parent_id=? \n");

				
		try
		{
			pstmt = DB.prepareStatement(sql.toString(),null);
			pstmt.setInt(1, getAD_Client_ID());
			if (HR_Department_ID != 0){
				pstmt.setInt(2, HR_Department_ID);
			}
			else if(HR_Block_ID !=0){
				pstmt.setInt(2, HR_Block_ID);
				
			}
			rs = pstmt.executeQuery();	
					
			while (rs.next ())
			{
				values = new RecordOfDepartment();
				values.setDepartment_id(rs.getInt(1));
				values.setDepartment(rs.getString(2));
				list.add(values);
			}
		}
		catch (SQLException e)
		{
			log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}	
		
		return list.toArray(new RecordOfDepartment[list.size()]);
	}

	
	class RecordOfDepartment{
		
		private int department_id;
		private String department;
		
		public int getDepartment_id() {
			return department_id;
		}
		public void setDepartment_id(int department_id) {
			this.department_id = department_id;
		}
		public String getDepartment() {
			return department;
		}
		public void setDepartment(String department) {
			this.department = department;
		}
		
	}

}
